﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace DAO
{
    public class MonAnDAO
    {
        /*public static List<MonAnDTO> SelectMonAnAll()
        {
            DataTable dataTable = DataProvider.ExecuteReader("sp_SelectMonAnAll");
            List<MonAnDTO> listMonAn = new List<MonAnDTO>();
            if (dataTable.Rows.Count > 0)
            {
                foreach (DataRow dataRow in dataTable.Rows)
                {
                    MonAnDTO monAn = new MonAnDTO();
                    monAn.Ma_Mon = dataRow["Ma_Mon"].ToString();
                    monAn.TenMon = dataRow["TenMon"].ToString();
                    monAn.Loai = dataRow["Loai"].ToString();
                    monAn.GiaTien = float.Parse(dataRow["GiaTien"].ToString());

                    listMonAn.Add(monAn);
                }
            }
            else
            {
                listMonAn = null;
            }
            return listMonAn;
        }
    */

        public static SqlConnection CreateConnection()
        {
            SqlConnection conn= new  SqlConnection(DataProvider.ConnectionString);
            return conn;
        }
        
        public static DataTable SelectMonAnAll(ref int tongMonAn )
        {
            SqlConnection conn = new SqlConnection(DataProvider.ConnectionString);
            SqlCommand cmd = new SqlCommand("sp_SelectMonAnAll", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter para = new SqlParameter("@tongMonAn", SqlDbType.Int);
            para.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(para);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable datatable = new DataTable();
            adapter.Fill(datatable);
            tongMonAn = (int)para.Value;
            conn.Close();

            return datatable;
        }

        public static MonAnDTO SelectMonAnById(string maMonAn)
        {
            List<SqlParameter> sqlParamas = new List<SqlParameter>();
            sqlParamas.Add(new SqlParameter("@maMon", maMonAn));
            DataTable dataTable = DataProvider.ExecuteReader("sp_SelectMonAnById", sqlParamas);

            MonAnDTO monAnDTO = new MonAnDTO();
            if (dataTable.Rows.Count > 0)
            {
                DataRow dataRow = dataTable.Rows[0];
                monAnDTO.Ma_Mon = dataRow["Ma_Mon"].ToString();
                monAnDTO.TenMon = dataRow["TenMon"].ToString();
                monAnDTO.Loai = dataRow["Loai"].ToString();
                monAnDTO.GiaTien = float.Parse(dataRow["GiaTien"].ToString());
            }
            else
            {
                monAnDTO = null;
            }
            return monAnDTO;
        }

        public static bool InsertMonAn(MonAnDTO monAnDTO)
        {
            List<SqlParameter> sqlParamas = new List<SqlParameter>();
            sqlParamas.Add(new SqlParameter("@maMon", monAnDTO.Ma_Mon));
            sqlParamas.Add(new SqlParameter("@tenMon", monAnDTO.TenMon));
            sqlParamas.Add(new SqlParameter("@loai", monAnDTO.Loai));
            sqlParamas.Add(new SqlParameter("@giaTien", monAnDTO.GiaTien));

            return DataProvider.ExecuteNoneQuery("sp_InsertMonAn",sqlParamas);
        }

        public static bool UpdateMonAnById(MonAnDTO monAnDTO)
        {
            List<SqlParameter> sqlParamas = new List<SqlParameter>();
            sqlParamas.Add(new SqlParameter("@maMon", monAnDTO.Ma_Mon));
            sqlParamas.Add(new SqlParameter("@tenMon", monAnDTO.TenMon));
            sqlParamas.Add(new SqlParameter("@loai", monAnDTO.Loai));
            sqlParamas.Add(new SqlParameter("@giaTien", monAnDTO.GiaTien));

            return DataProvider.ExecuteNoneQuery("sp_UpdateMonAnById", sqlParamas);
        }

        public static bool DeleteMonAnById(string maMon)
        {
            List<SqlParameter> sqlParamas = new List<SqlParameter>();
            sqlParamas.Add(new SqlParameter("@maMon", maMon));
            return DataProvider.ExecuteNoneQuery("sp_DeleteMonAnById", sqlParamas);
        
        }

        public static string CreateMonAnId()
        {
            DataTable dataTable = DataProvider.ExecuteReader("sp_CreateMonAnId", null);
            string ID = null;

            if (dataTable.Rows.Count > 0)
            {
                DataRow dataRow = dataTable.Rows[0];
                ID = dataRow[0].ToString();
            }

            return ID;
        }
    }
}
